package com.conversationboard.standalone.checkdb;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.conversationboard.database.Database;
import com.conversationboard.logger.Logger;
import com.conversationboard.model.User;

/**
 * Was replacing ' for &apos; in questions and first messages, so need to fix them. There are less than 1,000 of them, so few enough to do in a single
 * transaction.
 * 
 * @author Keith Watson
 *
 */

public class FixApostrophes {

	private static class Thread {

		private int boardId;
		private int threadId;
		private String title;


		public int getBoardId() {
			return boardId;
		}


		public void setBoardId(int boardId) {
			this.boardId = boardId;
		}


		public int getThreadId() {
			return threadId;
		}


		public void setThreadId(int threadId) {
			this.threadId = threadId;
		}


		public String getTitle() {
			return title;
		}


		public void setTitle(String title) {
			this.title = title;
		}

	}

	private static class Message {

		private int boardId;
		private int threadId;
		private int messageId;
		private String body;


		public int getBoardId() {
			return boardId;
		}


		public void setBoardId(int boardId) {
			this.boardId = boardId;
		}


		public int getThreadId() {
			return threadId;
		}


		public void setThreadId(int threadId) {
			this.threadId = threadId;
		}


		public String getBody() {
			return body;
		}


		public int getMessageId() {
			return messageId;
		}


		public void setMessageId(int messageId) {
			this.messageId = messageId;
		}


		public void setBody(String body) {
			this.body = body;
		}

	}


	public static void fixApostrophes() throws SQLException {

		Connection connection = null;

		List<Thread> threads = new ArrayList<Thread>();
		List<Message> messages = new ArrayList<Message>();

		try {

			connection = Database.getConnection();
			connection.setAutoCommit(false);

			/* Get all the offending threads and store the data */

			PreparedStatement statement = connection.prepareStatement("SELECT boardid, threadid, title FROM threads WHERE title LIKE '%&apos;%'");
			statement.execute();

			ResultSet resultSet = statement.getResultSet();

			while (resultSet.next()) {

				Thread thread = new Thread();
				thread.setBoardId(resultSet.getInt("boardid"));
				thread.setThreadId(resultSet.getInt("threadid"));
				thread.setTitle(resultSet.getString("title"));

				threads.add(thread);
			}

			statement.close();

			/* Get all the offending messages and store the data */

			PreparedStatement statement2 = connection.prepareStatement("SELECT boardid, threadid, messageid, body FROM messages WHERE body LIKE '%&apos;%'");
			statement2.execute();

			ResultSet resultSet2 = statement2.getResultSet();

			while (resultSet2.next()) {

				Message message = new Message();
				message.setBoardId(resultSet2.getInt("boardid"));
				message.setThreadId(resultSet2.getInt("threadid"));
				message.setMessageId(resultSet2.getInt("messageid"));
				message.setBody(resultSet2.getString("body"));

				messages.add(message);
			}

			statement2.close();

			int checkSum = threads.size() + messages.size();
			int testCheckSum = 0;

			/* Update all of the threads */

			PreparedStatement statement3 = connection.prepareStatement("UPDATE threads SET title=? WHERE boardid=? AND threadid=?");

			for (Thread thread : threads) {
				statement3.setString(1, removeApostrophes(thread.getTitle()));
				statement3.setInt(2, thread.getBoardId());
				statement3.setInt(3, thread.getThreadId());

				statement3.addBatch();
				System.out.println("Thread Title Change: [" + thread.getBoardId() + "-" + thread.getThreadId() + "]" + thread.getTitle());
				testCheckSum++;
			}

			statement3.executeBatch();
			statement3.close();

			PreparedStatement statement4 = connection.prepareStatement("UPDATE messages SET body=? WHERE boardid=? AND threadid=? AND messageid=?");

			for (Message message : messages) {
				statement4.setString(1, removeApostrophes(message.getBody()));
				statement4.setInt(2, message.getBoardId());
				statement4.setInt(3, message.getThreadId());
				statement4.setInt(4, message.getMessageId());

				statement4.addBatch();
				System.out.println("Message Change: [" + message.getBoardId() + "-" + message.getThreadId() + "-" + message.getMessageId() + "]" + message.getBody());
				testCheckSum++;
			}

			statement4.executeBatch();
			statement4.close();

			if (checkSum == testCheckSum) {
				connection.commit();
				Logger.log(new Date(), "Committed Apostrophe Changes.", User.getSystemUser());
			} else {
				connection.rollback();
				Logger.log(new Date(), "Rolled Back Apostrophe Changes - Checksum didn't match.", User.getSystemUser());
			}

		} catch (SQLException e) {
			connection.rollback();
			throw new SQLException(e);
		} finally {
			Database.close(connection);
		}
	}


	private static String removeApostrophes(String input) {
		return input.replaceAll("&apos;", "'");
	}

}
